package com.jy.api.util;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jy.api.domain.Product;
import com.jy.api.domain.ProductType;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLConnection;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;

public class DealWxTxtUtils {

    public static List readExcel(String txtUrl) {
        try {
            String fileType = txtUrl.substring(txtUrl.lastIndexOf(".") + 1);
            // 创建输入流，读取Excel
            URL url = new URL(txtUrl);
            // 打开连接
            URLConnection con = url.openConnection();
            // 输入流
            InputStream is = con.getInputStream();
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList = new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    List innerList = new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo;
                        if (sheet.getCell(j, i).getType().equals(CellType.DATE)) {
                            DateCell dc = (DateCell) sheet.getCell(j, i);
                            Date date = dc.getDate();
                            long time = (date.getTime() / 1000) - 60 * 60 * 8;
                            date.setTime(time * 1000);
                            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellinfo = formatter.format(date);
                        } else {
                            cellinfo = sheet.getCell(j, i).getContents();
                        }


                        innerList.add(cellinfo);
                        System.out.print(cellinfo);
                    }
                    outerList.add(i, innerList);
                    System.out.println();
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return new ArrayList();
        } catch (BiffException e) {
            e.printStackTrace();
            return new ArrayList();
        } catch (IOException e) {
            e.printStackTrace();
            return new ArrayList();
        }
        return new ArrayList();
    }



    public static List<LinkedHashMap<String, String>> alibabaEasyExcel(String txtUrl) throws Exception {
        String fileType = txtUrl.substring(txtUrl.lastIndexOf(".") + 1);

        URL url = new URL(txtUrl);
        // 打开连接
        URLConnection con = url.openConnection();
        InputStream is = con.getInputStream();
        ExcelReaderBuilder read = EasyExcel.read(is);
        List<LinkedHashMap<String, String>> readAll;
        fileType.toUpperCase();
        if (fileType.equals("csv")) {
            readAll = read.excelType(ExcelTypeEnum.CSV).charset(Charset.forName("GBK")).doReadAllSync();
        } else {
            readAll = read.doReadAllSync();
        }
        return readAll;
    }

    public static void main(String[] args) throws Exception {
        List<LinkedHashMap<String, String>> linkedHashMaps = alibabaEasyExcel("http://images.cdjkh.cn/upload/17313182378152.1318039569520897.xls");
        //{0=龙烟呈祥, 1=香烟, 2=15.0, 3=6, 4=10, 5=16}
        //吧台   仓库  平台可销
       // List<ProductType> productTypes = productTypeDao.findListByStoreIdAndEnabledOrderBySequenceAsc(params.getStoreId(),true);
      /*  HashMap map = new HashMap();
        for (ProductType productType : productTypes) {
            map.put(productType.getName(),productType);
        }*/
        for (LinkedHashMap<String, String> hashMap : linkedHashMaps) {
            Product product = new Product();
          //  product.setStoreId(params.getStoreId());
          //  product.setProductType((ProductType) map.get(hashMap.get("1")));
            product.setName(hashMap.get(0));
            product.setSalesPrice(Double.parseDouble(hashMap.get(2)));
            product.setQuantity(Integer.parseInt(hashMap.get(3)));
            product.setStockEnabled(true);
            product.setPurchaseEnabled(true);
            product.setShowEnabled(true);
         //   productDao.save(product);
        }


    }

}
